﻿using Dapper;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OMG.Fill.Potato.Model;
using OMG.Fill.Potato.Model.Ent;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
namespace OMG.Fill.Potato.Services
{
    /// <summary>
    /// 马铃薯销售、存储与加工情况统计表通用操作类
    /// </summary>
    public class PotSaleStoregeServices: PotSaleStoreges
    {
        public Pager<List<PotSaleStoregeEntity>> GetPageList(PotSaleStoregeQuery query)
        {
            Pager<List<PotSaleStoregeEntity>> obj = new Pager<List<PotSaleStoregeEntity>>();
            var paramlist = new DynamicParameters();
            string strWhereSql = "";//from 表在下面，需完善其他查询条件在这里接的写，格式为："and xxx=@xxx空格,如下注释部分代码所示："
            //if (!string.IsNullOrEmpty(query.GroupCode))
            //{
            //  strWhereSql += "and GroupCode = @GroupCode ";
            //  paramlist.Add("GroupCode", query.GroupCode);
            //}
            string strFromSql = "from dbo.T_PotSaleStorege where 1=1 " + strWhereSql;
            string strTotal = "select count(1)" + strFromSql + strWhereSql;
            string strOrderSql = "";//自定义完成order by语句
            string strSql = @"select * from (select *,ROW_NUMBER() over (order by Id ) as RowNumber " + strFromSql + ") as t ";
            strSql += "where t.RowNumber between  @minRow and @maxRow" + strOrderSql;
            paramlist.Add("minRow", query.MinRow);
            paramlist.Add("maxRow", query.MaxRow);
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    obj.rows = dbCon.Query<PotSaleStoregeEntity>(strSql,paramlist).ToList();
                    obj.total = dbCon.Query<int>(strTotal,paramlist).FirstOrDefault();
                    obj.status = DefaultKey.SuccessResult;
                    obj.message = "获取成功！";
                    dbCon.Dispose();//数据库连接释放
                    return obj;
                }
            }
            catch (Exception ex)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = ex.ToString().Trim();
                LogHelper.ErrorLog("获取马铃薯销售、存储与加工情况统计表分页列表失败", ex);
                return obj;
            }
        }
        public ResResult<SaleStoregeFillInModel> GetModel(string AreaCode, string FillInYear)
        {
            ReportServices reportService = new ReportServices();
            ResResult<SaleStoregeFillInModel> obj = new ResResult<SaleStoregeFillInModel>();
            SaleStoregeFillInModel model = new SaleStoregeFillInModel();
            int oldFillInYear = Convert.ToInt32(FillInYear) - 1;
            string strSql = "select t1.* from T_PotSaleStorege t1 join T_Report t2 on t1.ReportId = t2.Id";
            strSql += "  where t2.AreaCode = @AreaCode and t2.FillInYear = @FillInYear and ReportCode = 'PotSaleStorege' ";
            if (reportService.CheckRepCreate("PotSaleStorege", AreaCode, FillInYear))
            {
                obj.status = DefaultKey.ErrorResult;
                obj.model = model;
                obj.message = FillInYear + "年的全年生产情况表没有生成！";
            }
            else
            {
                ReportEntity reportModel = reportService.GetReportView("PotSaleStorege", AreaCode, FillInYear);
                model.ReportStatus = reportModel.Status;
                model.ReportId = reportModel.Id;
                model.Opinion = reportModel.Opinion;
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    if (reportService.CheckRepCount(model.ReportId, "T_PotSaleStorege"))
                    {
                        PotSaleStoregeEntity entity = new PotSaleStoregeEntity();
                        entity.ReportId = model.ReportId;
                        AddInfo(entity);
                    }
                    model.DataModel = dbCon.Query<PotSaleStoregeEntity>(strSql, new { AreaCode = AreaCode, FillInYear = FillInYear }).FirstOrDefault();
                    if (!reportService.CheckRepCreate("PotSaleStorege", AreaCode, oldFillInYear.ToString()))
                    {
                        model.OldDataModel = dbCon.Query<PotSaleStoregeEntity>(strSql, new { AreaCode = AreaCode, FillInYear = oldFillInYear.ToString() }).FirstOrDefault();
                    }
                }
                obj.model = model;
                obj.status = DefaultKey.SuccessResult;
                obj.message = "数据获取成功！";
            }
            return obj;
        }

        public ResResult<PotSaleStoregeEntity> GetDataModel(string ReportId)
        {
            ReportServices reportService = new ReportServices();
            ResResult<PotSaleStoregeEntity> obj = new ResResult<PotSaleStoregeEntity>();
            PotSaleStoregeEntity entity = new PotSaleStoregeEntity();
            string strSql = "select * from T_PotSaleStorege  where ReportId = @ReportId";
            if (reportService.CheckRepCount(ReportId, "T_PotSaleStorege"))
            {               
                entity.ReportId = ReportId;
                AddInfo(entity);
            }
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    entity = dbCon.Query<PotSaleStoregeEntity>(strSql, new { ReportId = ReportId }).FirstOrDefault();
                    dbCon.Dispose();//数据库连接释放
                }
                obj.model = entity;
                obj.status = DefaultKey.SuccessResult;
                obj.message = "数据获取成功！";
            }
            catch (Exception e)
            {
                obj.status = DefaultKey.ErrorResult;
                obj.message = e.Message;
            }
            return obj;
        }

        public PotSaleStoregeEntity ExcelToModel(string pathName, string fileSuffix, string ReportId)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;
            PotSaleStoregeEntity model = new PotSaleStoregeEntity();
            FileStream fs = new FileStream(pathName, FileMode.Open, FileAccess.Read);
            switch (fileSuffix)
            {
                case ".xls":
                    workbook = new HSSFWorkbook(fs);
                    break;
                case ".xlsx":
                    workbook = new XSSFWorkbook(fs);
                    break;
                default:
                    workbook = new HSSFWorkbook(fs);
                    break;
            }
            sheet = workbook.GetSheetAt(0);
            if(sheet != null)
            {
                int startRow = 4;
                IRow row = sheet.GetRow(startRow);//从第5行开始读
                if (row.GetCell(0) != null)
                {
                    if (row.GetCell(0).CellType == CellType.Formula)
                    {
                        model.ProductYield = Decimal.Round(Convert.ToDecimal(row.GetCell(0).NumericCellValue), 4);
                    }
                    else
                    {
                        if (DataCheck.isNumber(row.GetCell(0).ToString()))
                        {
                            model.ProductYield = Convert.ToDecimal(row.GetCell(0).ToString());
                        }
                        else
                        {
                            model.ProductYield = 0;
                        }
                    }
                }
                else
                {
                    model.ProductYield = 0;
                }
                if (row.GetCell(1) != null)
                {
                    if (row.GetCell(1).CellType == CellType.Formula)
                    {
                        model.SaleYield = Decimal.Round(Convert.ToDecimal(row.GetCell(1).NumericCellValue), 4);
                    }
                    else
                    {
                        if (DataCheck.isNumber(row.GetCell(1).ToString()))
                        {
                            model.SaleYield = Convert.ToDecimal(row.GetCell(1).ToString());
                        }
                        else
                        {
                            model.SaleYield = 0;
                        }
                    }
                }
                else
                {
                    model.SaleYield = 0;
                }

                if (row.GetCell(2) != null)
                {
                    if (row.GetCell(2).CellType == CellType.Formula)
                    {
                        model.PecSaleYield = Decimal.Round(Convert.ToDecimal(row.GetCell(2).NumericCellValue), 2);
                    }
                    else
                    {
                        if (DataCheck.isNumber(row.GetCell(2).ToString()))
                        {
                            model.PecSaleYield = Convert.ToDecimal(row.GetCell(2).ToString());
                        }
                        else
                        {
                            model.PecSaleYield = 0;
                        }
                    }
                }
                else
                {
                    model.PecSaleYield = 0;
                }

                if (row.GetCell(3) != null)
                {
                    if (row.GetCell(3).CellType == CellType.Formula)
                    {
                        model.ZLyield = Decimal.Round(Convert.ToDecimal(row.GetCell(3).NumericCellValue), 4);
                    }
                    else
                    {
                        if (DataCheck.isNumber(row.GetCell(3).ToString()))
                        {
                            model.ZLyield = Convert.ToDecimal(row.GetCell(3).ToString());
                        }
                        else
                        {
                            model.ZLyield = 0;
                        }
                    }
                }
                else
                {
                    model.ZLyield = 0;
                }

                if (row.GetCell(4) != null)
                {
                    if (row.GetCell(4).CellType == CellType.Formula)
                    {
                        model.XSyield = Decimal.Round(Convert.ToDecimal(row.GetCell(4).NumericCellValue), 4);
                    }
                    else
                    {
                        if (DataCheck.isNumber(row.GetCell(4).ToString()))
                        {
                            model.XSyield = Convert.ToDecimal(row.GetCell(4).ToString());
                        }
                        else
                        {
                            model.XSyield = 0;
                        }
                    }
                }
                else
                {
                    model.XSyield = 0;
                }

                if (row.GetCell(5) != null)
                {
                    if (row.GetCell(5).CellType == CellType.Formula)
                    {
                        model.SYyield = Decimal.Round(Convert.ToDecimal(row.GetCell(5).NumericCellValue), 4);
                    }
                    else
                    {
                        if (DataCheck.isNumber(row.GetCell(5).ToString()))
                        {
                            model.SYyield = Convert.ToDecimal(row.GetCell(5).ToString());
                        }
                        else
                        {
                            model.SYyield = 0;
                        }
                    }
                }
                else
                {
                    model.SYyield = 0;
                }

                if (row.GetCell(6) != null)
                {
                    if (row.GetCell(6).CellType == CellType.Formula)
                    {
                        model.ZSyield = Decimal.Round(Convert.ToDecimal(row.GetCell(6).NumericCellValue), 4);
                    }
                    else
                    {
                        if (DataCheck.isNumber(row.GetCell(6).ToString()))
                        {
                            model.ZSyield = Convert.ToDecimal(row.GetCell(6).ToString());
                        }
                        else
                        {
                            model.ZSyield = 0;
                        }
                    }
                }
                else
                {
                    model.ZSyield = 0;
                }

                if (row.GetCell(7) != null)
                {
                    if (row.GetCell(7).CellType == CellType.Formula)
                    {
                        model.ProductOutput = Decimal.Round(Convert.ToDecimal(row.GetCell(7).NumericCellValue), 4);
                    }
                    else
                    {
                        if (DataCheck.isNumber(row.GetCell(7).ToString()))
                        {
                            model.ProductOutput = Convert.ToDecimal(row.GetCell(7).ToString());
                        }
                        else
                        {
                            model.ProductOutput = 0;
                        }
                    }
                }
                else
                {
                    model.ProductOutput = 0;
                }

                if (row.GetCell(8) != null)
                {
                    if (row.GetCell(8).CellType == CellType.Formula)
                    {
                        model.SaleOutput = Decimal.Round(Convert.ToDecimal(row.GetCell(8).NumericCellValue), 4);
                    }
                    else
                    {
                        if (DataCheck.isNumber(row.GetCell(8).ToString()))
                        {
                            model.SaleOutput = Convert.ToDecimal(row.GetCell(8).ToString());
                        }
                        else
                        {
                            model.SaleOutput = 0;
                        }
                    }
                }
                else
                {
                    model.SaleOutput = 0;
                }

                if (row.GetCell(9) != null)
                {
                    if (row.GetCell(9).CellType == CellType.Formula)
                    {
                        model.PecSaleOutput = Decimal.Round(Convert.ToDecimal(row.GetCell(9).NumericCellValue), 2);
                    }
                    else
                    {
                        if (DataCheck.isNumber(row.GetCell(9).ToString()))
                        {
                            model.PecSaleOutput = Convert.ToDecimal(row.GetCell(9).ToString());
                        }
                        else
                        {
                            model.PecSaleOutput = 0;
                        }
                    }
                }
                else
                {
                    model.PecSaleOutput = 0;
                }
                if (row.GetCell(10) != null)
                    model.SaleZone = row.GetCell(10).ToString();
                if (row.GetCell(11) != null)
                {
                    if (row.GetCell(11).CellType == CellType.Formula)
                    {
                        model.StorageNum = Decimal.Round(Convert.ToDecimal(row.GetCell(11).NumericCellValue), 4);
                    }
                    else
                    {
                        if (DataCheck.isNumber(row.GetCell(11).ToString()))
                        {
                            model.StorageNum = Convert.ToDecimal(row.GetCell(11).ToString());
                        }
                        else
                        {
                            model.StorageNum = 0;
                        }
                    }
                }
                else
                {
                    model.StorageNum = 0;
                }
                if (row.GetCell(12) != null)
                    model.StorageWay = row.GetCell(12).ToString();
                if (row.GetCell(13) != null)
                {
                    if (row.GetCell(13).CellType == CellType.Formula)
                    {
                        model.Stock = Decimal.Round(Convert.ToDecimal(row.GetCell(13).NumericCellValue),4);
                    }
                    else
                    {
                        if (DataCheck.isNumber(row.GetCell(13).ToString()))
                        {
                            model.Stock = Convert.ToDecimal(row.GetCell(13).ToString());
                        }
                        else
                        {
                            model.Stock = 0;
                        }
                    }
                }
                else
                {
                    model.Stock = 0;
                }
                if (row.GetCell(14) != null)
                {
                    if (row.GetCell(14).CellType == CellType.Formula)
                    {
                        model.DFyield = Decimal.Round(Convert.ToDecimal(row.GetCell(14).NumericCellValue), 4);
                    }
                    else
                    {
                        if (DataCheck.isNumber(row.GetCell(14).ToString()))
                        {
                            model.DFyield = Convert.ToDecimal(row.GetCell(14).ToString());
                        }
                        else
                        {
                            model.DFyield = 0;
                        }
                    }
                }
                else
                {
                    model.DFyield = 0;
                }

                if (row.GetCell(15) != null)
                {
                    if (row.GetCell(15).CellType == CellType.Formula)
                    {
                        model.DFoutput = Decimal.Round(Convert.ToDecimal(row.GetCell(15).NumericCellValue), 4);
                    }
                    else
                    {
                        if (DataCheck.isNumber(row.GetCell(15).ToString()))
                        {
                            model.DFoutput = Convert.ToDecimal(row.GetCell(15).ToString());
                        }
                        else
                        {
                            model.DFoutput = 0;
                        }
                    }
                }
                else
                {
                    model.DFoutput = 0;
                }

                if (row.GetCell(16) != null)
                {
                    if (row.GetCell(16).CellType == CellType.Formula)
                    {
                        model.SPyield = Decimal.Round(Convert.ToDecimal(row.GetCell(16).NumericCellValue), 4);
                    }
                    else
                    {
                        if (DataCheck.isNumber(row.GetCell(16).ToString()))
                        {
                            model.SPyield = Convert.ToDecimal(row.GetCell(16).ToString());
                        }
                        else
                        {
                            model.SPyield = 0;
                        }
                    }
                }
                else
                {
                    model.SPyield = 0;
                }

                if (row.GetCell(17) != null)
                {
                    if (row.GetCell(17).CellType == CellType.Formula)
                    {
                        model.SPoutput = Decimal.Round(Convert.ToDecimal(row.GetCell(17).NumericCellValue), 4);
                    }
                    else
                    {
                        if (DataCheck.isNumber(row.GetCell(17).ToString()))
                        {
                            model.SPoutput = Convert.ToDecimal(row.GetCell(17).ToString());
                        }
                        else
                        {
                            model.SPoutput = 0;
                        }
                    }
                }
                else
                {
                    model.SPoutput = 0;
                }

                if (row.GetCell(18) != null)
                {
                    if (row.GetCell(18).CellType == CellType.Formula)
                    {
                        model.OPyield = Decimal.Round(Convert.ToDecimal(row.GetCell(18).NumericCellValue), 4);
                    }
                    else
                    {
                        if (DataCheck.isNumber(row.GetCell(18).ToString()))
                        {
                            model.OPyield = Convert.ToDecimal(row.GetCell(18).ToString());
                        }
                        else
                        {
                            model.OPyield = 0;
                        }
                    }
                }
                else
                {
                    model.OPyield = 0;
                }
                /////////////////////////////////////////////
                if (row.GetCell(19) != null)
                {
                    if (row.GetCell(19).CellType == CellType.Formula)
                    {
                        model.OPoutput = Decimal.Round(Convert.ToDecimal(row.GetCell(19).NumericCellValue),4);
                    }
                    else
                    {
                        if (DataCheck.isNumber(row.GetCell(19).ToString()))
                        {
                            model.OPoutput = Convert.ToDecimal(row.GetCell(19).ToString());
                        }
                        else
                        {
                            model.OPoutput = 0;
                        }
                    }
                }
                else
                {
                    model.OPoutput = 0;
                }
                if (row.GetCell(20) != null)
                    model.Remark = row.GetCell(20).ToString();
                model.ReportId = ReportId;
            }
            return model;
        }

        public ResResult<List<SaleStoregeStatisModel>> getStatisListData(string AreaCode, string FillInYear)
        {
            ResResult<List<SaleStoregeStatisModel>> obj = new ResResult<List<SaleStoregeStatisModel>>();
            List<SaleStoregeStatisModel> list = new List<SaleStoregeStatisModel>();
            AreaServices areaServices = new AreaServices();
            int oldFillInYear = Convert.ToInt16(FillInYear) - 1;
            if (AreaCode.Equals("530000000000"))
            {
                var citylist = areaServices.GetPrefectureList();
                SaleStoregeStatisModel totalModel = getProvinStatis(FillInYear);
                SaleStoregeStatisModel oldtotalModel = getProvinStatis(oldFillInYear.ToString());
                if (totalModel != null)
                {
                    totalModel.AreaCode = AreaCode;
                    totalModel.AreaName = "合计";
                    totalModel.tag = 2;
                    if (oldtotalModel != null)
                    {
                        if (oldtotalModel.SaleYield != 0)
                        {
                            totalModel.PecSaleYield = Decimal.Round((decimal)((totalModel.SaleYield - oldtotalModel.SaleYield) / oldtotalModel.SaleYield), 2);
                        }
                        if (oldtotalModel.SaleOutput != 0)
                        {
                            totalModel.PecSaleOutput = Decimal.Round((decimal)((totalModel.SaleOutput - oldtotalModel.SaleOutput) / oldtotalModel.SaleOutput), 2);
                        }
                    }
                } 
               
                foreach (string code in citylist)
                {
                    SaleStoregeStatisModel model = getTotalStatis(code, FillInYear);
                    
                    SaleStoregeStatisModel oldmodel = getTotalStatis(AreaCode, oldFillInYear.ToString());
                    if (model != null)
                    {
                        model.AreaCode = code;
                        model.AreaName = areaServices.GetByCodeModel(code).model.FullName;
                        model.tag = 1;
                        if (oldmodel != null)
                        {
                            if (oldmodel.SaleYield != 0)
                            {
                                model.PecSaleYield = Decimal.Round((decimal)((model.SaleYield - oldmodel.SaleYield) / oldmodel.SaleYield), 2);
                            }
                            if (oldmodel.SaleOutput != 0)
                            {
                                model.PecSaleOutput = Decimal.Round((decimal)((model.SaleOutput - oldmodel.SaleOutput) / oldmodel.SaleOutput), 2);
                            }
                        }
                        list.Add(model);
                    }
                }
                list.Add(totalModel);
            }
            else if (AreaCode.Substring(4, 8).Equals("00000000"))
            {
                List<SaleStoregeStatisModel> tlist = getAreaStatisList(AreaCode, FillInYear);
                SaleStoregeStatisModel model = getTotalStatis(AreaCode, FillInYear);
                SaleStoregeStatisModel oldmodel = getTotalStatis(AreaCode, oldFillInYear.ToString());
                if (tlist.Count > 0)
                {
                    list.AddRange(tlist);
                }
                if(model != null)
                {
                    if(oldmodel != null)
                    {
                        if(oldmodel.SaleYield != 0)
                        {
                            model.PecSaleYield = Decimal.Round((decimal)((model.SaleYield - oldmodel.SaleYield) / oldmodel.SaleYield),2);
                        }
                        if (oldmodel.SaleOutput != 0)
                        {
                            model.PecSaleOutput = Decimal.Round((decimal)((model.SaleOutput - oldmodel.SaleOutput) / oldmodel.SaleOutput), 2);
                        }
                    }
                    model.AreaName = areaServices.GetByCodeModel(AreaCode).model.FullName; ;
                    model.AreaCode = AreaCode;
                    list.Add(model);
                }

            }
            obj.model = list;
            obj.status = DefaultKey.SuccessResult;
            obj.message = "获取成功";
            return obj;
        }
        public SaleStoregeStatisModel getProvinStatis(string FillInYear)
        {
            SaleStoregeStatisModel model = new SaleStoregeStatisModel();
            string strSql = "select  ";
            strSql += " isnull(sum(t3.ProductYield),0.00) ProductYield, ";
            strSql += " isnull(sum(t3.SaleYield),0.00) SaleYield, ";
            strSql += " isnull(sum(t3.ZLyield),0.00) ZLyield, ";
            strSql += " isnull(sum(t3.XSyield),0.00) XSyield, ";
            strSql += " isnull(sum(t3.SYyield),0.00) SYyield,  ";
            strSql += " isnull(sum(t3.ZSyield),0.00) ZSyield, ";
            strSql += " isnull(sum(t3.ProductOutput),0.00) ProductOutput, ";
            strSql += " isnull(sum(t3.SaleOutput),0.00) SaleOutput, ";
            strSql += " isnull(sum(t3.StorageNum),0.00) StorageNum,";
            strSql += " isnull(sum(t3.Stock),0.00) Stock, ";
            strSql += " isnull(sum(t3.DFyield),0.00) DFyield, ";
            strSql += " isnull(sum(t3.DFoutput),0.00) DFoutput, ";
            strSql += " isnull(sum(t3.SPyield),0.00) SPyield, ";
            strSql += " isnull(sum(t3.SPoutput),0.00) SPoutput,";
            strSql += " isnull(sum(t3.OPyield),0.00) OPyield,";
            strSql += " isnull(sum(t3.OPoutput),0.00) OPoutput from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code left join  ";
            strSql += " T_PotSaleStorege t3 on t2.Id = t3.ReportId where t2.ReportCode = 'PotSaleStorege' and t1.ParentCode != '530000000000' and t1.Code !='530000000000' and t2.FillInYear = @FillInYear  ";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    model = dbCon.Query<SaleStoregeStatisModel>(strSql, new { FillInYear = FillInYear }).FirstOrDefault();
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取马铃薯生产情况统计列表信息失败", ex);
            }
            return model;
        }
        public SaleStoregeStatisModel getTotalStatis(string AreaCode, string FillInYear)
        {
            SaleStoregeStatisModel model = new SaleStoregeStatisModel();
            string strSql = "select  ";
            strSql += " isnull(sum(t3.ProductYield),0.00) ProductYield, ";
            strSql += " isnull(sum(t3.SaleYield),0.00) SaleYield, ";
            strSql += " isnull(sum(t3.ZLyield),0.00) ZLyield, ";
            strSql += " isnull(sum(t3.XSyield),0.00) XSyield, ";
            strSql += " isnull(sum(t3.SYyield),0.00) SYyield,  ";
            strSql += " isnull(sum(t3.ZSyield),0.00) ZSyield, ";
            strSql += " isnull(sum(t3.ProductOutput),0.00) ProductOutput, ";
            strSql += " isnull(sum(t3.SaleOutput),0.00) SaleOutput, ";
            strSql += " isnull(sum(t3.StorageNum),0.00) StorageNum,";
            strSql += " isnull(sum(t3.Stock),0.00) Stock, ";
            strSql += " isnull(sum(t3.DFyield),0.00) DFyield, ";
            strSql += " isnull(sum(t3.DFoutput),0.00) DFoutput, ";
            strSql += " isnull(sum(t3.SPyield),0.00) SPyield, ";
            strSql += " isnull(sum(t3.SPoutput),0.00) SPoutput,";
            strSql += " isnull(sum(t3.OPyield),0.00) OPyield,";
            strSql += " isnull(sum(t3.OPoutput),0.00) OPoutput from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code left join  ";
            strSql += " T_PotSaleStorege t3 on t2.Id = t3.ReportId where t2.ReportCode = 'PotSaleStorege' and t1.ParentCode = @AreaCode and t2.FillInYear = @FillInYear  ";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    model = dbCon.Query<SaleStoregeStatisModel>(strSql, new { AreaCode = AreaCode, FillInYear = FillInYear }).FirstOrDefault();
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取马铃薯生产情况统计列表信息失败", ex);
            }
            return model;
        }
        public List<SaleStoregeStatisModel> getAreaStatisList(string AreaCode, string FillInYear)
        {
            List<SaleStoregeStatisModel> list = new List<SaleStoregeStatisModel>();
            string strSql = "select t1.FullName  AreaName, ";
            strSql += " isnull(t3.ProductYield,0.00) ProductYield, ";
            strSql += " isnull(t3.SaleYield,0.00) SaleYield, ";
            strSql += " isnull(t3.ZLyield,0.00) ZLyield, ";
            strSql += " isnull(t3.XSyield,0.00) XSyield, ";
            strSql += " isnull(t3.SYyield,0.00) SYyield,  ";
            strSql += " isnull(t3.ZSyield,0.00) ZSyield, ";
            strSql += " isnull(t3.ProductOutput,0.00) ProductOutput, ";
            strSql += " isnull(t3.SaleOutput,0.00) SaleOutput, ";
            strSql += " isnull(t3.StorageNum,0.00) StorageNum,";
            strSql += " isnull(t3.Stock,0.00) Stock, ";
            strSql += " isnull(t3.DFyield,0.00) DFyield, ";
            strSql += " isnull(t3.DFoutput,0.00) DFoutput, ";
            strSql += " isnull(t3.SPyield,0.00) SPyield, ";
            strSql += " isnull(t3.SPoutput,0.00) SPoutput,";
            strSql += " isnull(t3.OPyield,0.00) OPyield,";
            strSql += " isnull(t3.OPoutput,0.00) OPoutput from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code left join  ";
            strSql += " T_PotSaleStorege t3 on t2.Id = t3.ReportId where t2.ReportCode = 'PotSaleStorege' and t1.ParentCode = @AreaCode and t2.FillInYear = @FillInYear  ";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<SaleStoregeStatisModel>(strSql, new { AreaCode = AreaCode, FillInYear = FillInYear }).ToList();
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取马铃薯生产情况统计列表信息失败", ex);
            }
            return list;
        }

        public ResResult<List<List<AllYearStatisModel>>> GetAllYearStatisList(string AreaCode)
        {
            ResResult<List<List<AllYearStatisModel>>> obj = new ResResult<List<List<AllYearStatisModel>>>();

            List<List<AllYearStatisModel>> list = new List<List<AllYearStatisModel>>();
            AreaServices areaservices = new AreaServices();
            DictServices dictService = new DictServices();
            var yearList = dictService.GetYearList();
            var childAreaList = areaservices.getChildAreaCode(AreaCode);
            if (AreaCode.Equals("530000000000"))
            {
                foreach (var item in childAreaList)
                {
                    var statislist = GetAllYearCityData(item.Code);
                    var datalist = ParseData(yearList, statislist);
                    List<AllYearStatisModel> templist = new List<AllYearStatisModel>();
                    for (int i = 0; i < datalist.Count; i++)
                    {
                        var dataItem = datalist[i];
                        AllYearStatisModel model = new AllYearStatisModel();
                        model.AreaName = item.Name;
                        model.allYearDataList = dataItem;
                        templist.Add(model);
                    }
                    list.Add(templist);
                }
                var totalstatislist = GetAllYearProvinData();
                var totaldatalist = ParseData(yearList, totalstatislist);
                List<AllYearStatisModel> totaltemplist = new List<AllYearStatisModel>();
                for (int i = 0; i < totaldatalist.Count; i++)
                {
                    var dataItem = totaldatalist[i];
                    AllYearStatisModel model = new AllYearStatisModel();
                    model.AreaName = "全省";
                    model.allYearDataList = dataItem;
                    totaltemplist.Add(model);
                }
                list.Add(totaltemplist);
            }
            else if (AreaCode.Substring(4, 8).Equals("00000000"))
            {
                foreach (var item in childAreaList)
                {
                    var statislist = GetAllYearCountyData(item.Code);
                    var datalist = ParseData(yearList, statislist);
                    List<AllYearStatisModel> templist = new List<AllYearStatisModel>();
                    for (int i = 0; i < datalist.Count; i++)
                    {
                        var dataItem = datalist[i];
                        AllYearStatisModel model = new AllYearStatisModel();
                        model.AreaName = item.Name;
                        model.allYearDataList = dataItem;
                        templist.Add(model);
                    }
                    list.Add(templist);
                }
                var totalstatislist = GetAllYearCityData(AreaCode);
                var totaldatalist = ParseData(yearList, totalstatislist);
                List<AllYearStatisModel> totaltemplist = new List<AllYearStatisModel>();
                for (int i = 0; i < totaldatalist.Count; i++)
                {
                    var dataItem = totaldatalist[i];
                    AllYearStatisModel model = new AllYearStatisModel();
                    model.AreaName = "全州（市）";
                    model.allYearDataList = dataItem;
                    totaltemplist.Add(model);
                }
                list.Add(totaltemplist);
            }
            obj.model = list;
            obj.status = DefaultKey.SuccessResult;
            obj.message = "获取成功";
            return obj;
        }
        /// <summary>
        /// 获取区县级所有年份统计数据
        /// </summary>
        /// <param name="AreaCode"></param>
        /// <returns></returns>
        public List<SaleStoregeStatisModel> GetAllYearCountyData(string AreaCode)
        {
            List<SaleStoregeStatisModel> list = new List<SaleStoregeStatisModel>();
            string strSql = "select t1.Name AreaName,t4.DictValue FillInYear,t3.* ";
            strSql += " from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code left join ";
            strSql += " T_PotSaleStorege t3 on t2.Id = t3.ReportId right join T_Dict t4 on t2.FillInYear = t4.DictValue";
            strSql += " where t2.ReportCode = 'PotSaleStorege' and t2.AreaCode = @AreaCode ";
            strSql += " and GroupCode='ReportYear' and t4.DictText !='全部' ";
            strSql += "  and t2.status=  '4' ";
            strSql += " order by t4.DictValue asc";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<SaleStoregeStatisModel>(strSql, new { AreaCode = AreaCode }).ToList();
                    dbCon.Dispose();//数据库连接释放
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取马铃薯生产情况统计列表信息失败", ex);
            }
            return list;
        }
        /// <summary>
        /// 分解属性成各个列表，共18个前端所需显示指标列表
        /// </summary>
        /// <param name="YearList"></param>
        /// <param name="DataList"></param>
        /// <returns></returns>
        public List<List<decimal?>> ParseData(List<string> YearList, List<SaleStoregeStatisModel> DataList)
        {
            List<List<decimal?>> list = new List<List<decimal?>>();
            List<decimal?> list1 = new List<decimal?>(),
                list2 = new List<decimal?>(), list3 = new List<decimal?>(),
                list4 = new List<decimal?>(), list5 = new List<decimal?>(),
                list6 = new List<decimal?>(), list7 = new List<decimal?>(),
                list8 = new List<decimal?>(), list9 = new List<decimal?>(),
                list10 = new List<decimal?>(), list11 = new List<decimal?>(),
                list12 = new List<decimal?>(), list13 = new List<decimal?>(),
                list14 = new List<decimal?>(), list15 = new List<decimal?>(),
                list16 = new List<decimal?>(), list17 = new List<decimal?>(),
                list18 = new List<decimal?>();

            foreach (var year in YearList)
            {
                var preYear = (int.Parse(year) - 1).ToString();
                decimal? temp1 = 0, temp2 = 0, temp3 = 0, temp4 = 0, temp5 = 0, temp6 = 0, 
                temp7 = 0, temp8 = 0, temp9 = 0, temp10 = 0, temp11 = 0, temp12 = 0, temp13 = 0, 
                temp14 = 0, temp15 = 0, temp16 = 0, temp17 = 0, temp18 = 0;
                if (DataList != null && DataList.Count > 0)
                {
                    for (int i = 0; i < DataList.Count; i++)
                    {
                        var Item = DataList[i];
                        if (Item.FillInYear.Equals(year))
                        {
                            temp1 = Item.ProductYield;
                            temp2 = Item.SaleYield;
                           // temp3 = Item.PecSaleYield;
                            temp4 = Item.ZLyield;
                            temp5 = Item.XSyield;
                            temp6 = Item.SYyield;
                            temp7 = Item.ZSyield;
                            temp8 = Item.ProductOutput;
                            temp9 = Item.SaleOutput;
                           // temp10 = Item.PecSaleOutput;
                            temp11 = Item.StorageNum;

                            temp12 = Item.Stock;
                            temp13 = Item.DFyield;
                            temp14 = Item.DFoutput;
                            temp15 = Item.SPyield;
                            temp16 = Item.SPoutput;
                            temp17 = Item.OPyield;
                            temp18 = Item.OPoutput;
                        }
                    }
                    for (int j = 0; j < DataList.Count; j++)
                    {
                        var preItem = DataList[j];
                        if (preItem.FillInYear.Equals(preYear))
                        {
                           if(preItem.SaleYield != null && preItem.SaleYield != 0)
                            {
                                temp3 = Decimal.Round((decimal)((temp2 - preItem.SaleYield) / preItem.SaleYield), 2);
                            }
                            if (preItem.SaleOutput != null && preItem.SaleOutput != 0)
                            {
                                temp10 = Decimal.Round((decimal)((temp9 - preItem.SaleOutput) / preItem.SaleOutput), 2);
                            }
                        }
                    }
                }
                list1.Add(temp1); list2.Add(temp2); list3.Add(temp3); list4.Add(temp4); list5.Add(temp5); list6.Add(temp6);
                list7.Add(temp7); list8.Add(temp8); list9.Add(temp9); list10.Add(temp10); list11.Add(temp11); list12.Add(temp12);
                list13.Add(temp13); list14.Add(temp14); list15.Add(temp15); list16.Add(temp16); list17.Add(temp17); list18.Add(temp18);
            }
            //按顺序加入list
            list.Add(list1); list.Add(list2); list.Add(list3); list.Add(list4); list.Add(list5); list.Add(list6); list.Add(list7);
            list.Add(list8); list.Add(list9); list.Add(list10); list.Add(list11); list.Add(list12); list.Add(list13); list.Add(list14);
            list.Add(list15); list.Add(list16); list.Add(list17); list.Add(list18); 

            return list;
        }

        /// <summary>
        /// 获取州市级所有年份统计数据
        /// </summary>
        /// <param name="AreaCode"></param>
        /// <returns></returns>
        public List<SaleStoregeStatisModel> GetAllYearCityData(string AreaCode)
        {
            List<SaleStoregeStatisModel> list = new List<SaleStoregeStatisModel>();
            string strSql = "select t4.DictValue FillInYear, ";
            strSql += " isnull(sum(t3.ProductYield),0.00) ProductYield, ";
            strSql += " isnull(sum(t3.SaleYield),0.00) SaleYield, ";
            strSql += " isnull(sum(t3.ZLyield),0.00) ZLyield, ";
            strSql += " isnull(sum(t3.XSyield),0.00) XSyield,";
            strSql += " isnull(sum(t3.SYyield),0.00) SYyield, ";
            strSql += " isnull(sum(t3.ZSyield),0.00) ZSyield, ";
            strSql += " isnull(sum(t3.ProductOutput),0.00) ProductOutput, ";
            strSql += " isnull(sum(t3.SaleOutput),0.00) SaleOutput, ";
            strSql += " isnull(sum(t3.StorageNum),0.00) StorageNum, ";
            strSql += " isnull(sum(t3.Stock),0.00) Stock,  ";
            strSql += " isnull(sum(t3.DFyield),0.00) DFyield, ";
            strSql += " isnull(sum(t3.DFoutput),0.00) DFoutput, ";
            strSql += " isnull(sum(t3.SPyield),0.00) SPyield, ";
            strSql += " isnull(sum(t3.SPoutput),0.00) SPoutput, ";
            strSql += " isnull(sum(t3.OPyield),0.00) OPyield, ";
            strSql += " isnull(sum(t3.OPoutput),0.00) OPoutput ";
            strSql += " from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code left join  ";
            strSql += " T_PotSaleStorege t3 on t2.Id = t3.ReportId right join T_Dict t4 on t2.FillInYear = t4.DictValue ";
            strSql += "  where t2.ReportCode = 'PotSaleStorege' and t1.ParentCode =@AreaCode ";
            strSql += " and GroupCode='ReportYear' and t4.DictText !='全部' ";
            strSql += "  and t2.status=  '4' ";
           
            strSql += " group by t4.DictValue  order by t4.DictValue asc";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<SaleStoregeStatisModel>(strSql, new { AreaCode = AreaCode }).ToList();
                    dbCon.Dispose();//数据库连接释放
                    
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取马铃薯生产情况统计列表信息失败", ex);
            }
            return list;
        }

        /// <summary>
        /// 获取全省所有年度统计数据
        /// </summary>
        /// <returns></returns>
        public List<SaleStoregeStatisModel> GetAllYearProvinData()
        {
            List<SaleStoregeStatisModel> list = new List<SaleStoregeStatisModel>();
            string strSql = "select t4.DictValue FillInYear, ";
            strSql += " isnull(sum(t3.ProductYield),0.00) ProductYield, ";
            strSql += " isnull(sum(t3.SaleYield),0.00) SaleYield, ";
            strSql += " isnull(sum(t3.ZLyield),0.00) ZLyield, ";
            strSql += " isnull(sum(t3.XSyield),0.00) XSyield,";
            strSql += " isnull(sum(t3.SYyield),0.00) SYyield, ";
            strSql += " isnull(sum(t3.ZSyield),0.00) ZSyield, ";
            strSql += " isnull(sum(t3.ProductOutput),0.00) ProductOutput, ";
            strSql += " isnull(sum(t3.SaleOutput),0.00) SaleOutput, ";
            strSql += " isnull(sum(t3.StorageNum),0.00) StorageNum, ";
            strSql += " isnull(sum(t3.Stock),0.00) Stock,  ";
            strSql += " isnull(sum(t3.DFyield),0.00) DFyield, ";
            strSql += " isnull(sum(t3.DFoutput),0.00) DFoutput, ";
            strSql += " isnull(sum(t3.SPyield),0.00) SPyield, ";
            strSql += " isnull(sum(t3.SPoutput),0.00) SPoutput, ";
            strSql += " isnull(sum(t3.OPyield),0.00) OPyield, ";
            strSql += " isnull(sum(t3.OPoutput),0.00) OPoutput ";
            strSql += " from T_Area t1 left join ";
            strSql += " T_Report t2 on t2.AreaCode = t1.Code left join  ";
            strSql += " T_PotSaleStorege t3 on t2.Id = t3.ReportId right join T_Dict t4 on t2.FillInYear = t4.DictValue ";
            strSql += "  where t2.ReportCode = 'PotSaleStorege' and t1.ParentCode !='530000000000'  and t1.ParentCode is not null ";
            strSql += " and GroupCode='ReportYear' and t4.DictText !='全部' ";
            strSql += "  and t2.status=  '4' ";
            
            strSql += " group by t4.DictValue  order by t4.DictValue asc";
            try
            {
                using (var dbCon = ConnectFactory.GetOpenConnection())
                {
                    list = dbCon.Query<SaleStoregeStatisModel>(strSql).ToList();
                    dbCon.Dispose();//数据库连接释放
                   
                    
                }
            }
            catch (Exception ex)
            {
                return null;
                LogHelper.ErrorLog("获取马铃薯生产情况统计列表信息失败", ex);
            }
            return list;
        }



    }
}